package mobilizator.app.db;

import java.util.ArrayList;

import mobilizator.app.R;
import mobilizator.app.data.Category;
import mobilizator.app.data.Cloth;
import mobilizator.app.data.ClothSet;
import mobilizator.app.data.ClothingAttribute;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class MobilizatorDb {

	private static MyDatabaseHelper dh = null;
	private static SQLiteDatabase db = null;

	private Context context;

	// ------------- TABLE NAME COSTANTS ------------------
	private static final String TBL_CLOTHING_ATTRIBS = "cloth_attrib";
	private static final String TBL_CLOTHES = "clothes";
	private static final String TBL_CATEGORIES = "categories";
	private static final String TBL_CLOTHES_ATRIBS_ASSOC = "clothes_atribs_pairs";
	private static final String TBL_CLOTHES_CATS_ASSOC = "clothes_cats_pairs";
	private static final String TBL_CLOTHSETS = "cloth_sets";
	private static final String TBL_CLOTHES_SETS_ASSOC = "clothes_sets_pairs";
	// ----------------------------------------------------

	// ------------- TABLE COLUMN COSTANTS ------------------
	private static final String CLOTHING_ATTRIBS_ID = "_id";
	private static final String CLOTHING_ATTRIBS_NAME = "name";
	private static final String CLOTHING_ATTRIBS_TYPE = "type";

	private static final String CLOTHES_ID = "_id";
	private static final String CLOTHES_NAME = "name";
	private static final String CLOTHES_DESCRIPTION = "description";

	private static final String CLOTHES_CATS_ASSOC_ID = "_id";
	private static final String CLOTHES_CATS_ASSOC_ID_CLOTH = "id_cloth";
	private static final String CLOTHES_CATS_ASSOC_ID_CATEGORY = "id_cat";

	private static final String CLOTHES_ATRIBS_ASSOC_ID = "_id";
	private static final String CLOTHES_ATRIBS_ASSOC_ID_CLOTH = "id_cloth";
	private static final String CLOTHES_ATRIBS_ASSOC_ID_ATTRIB = "id_attrib";
	private static final String CLOTHES_ATRIBS_ASSOC_VALUE = "value";

	private static final String CATEGORIES_ID = "_id";
	private static final String CATEGORIES_NAME = "name";
	private static final String CATEGORIES_DESCRIPTION = "description";
	private static final String CATEGORIES_PARENT = "parent";

	private static final String CLOTHSETS_ID = "_id";
	private static final String CLOTHSETS_NAME = "name";
	private static final String CLOTHSETS_DESCRIPTION = "description";

	private static final String CLOTHES_SETS_ASSOC_ID = "_id";
	private static final String CLOTHES_SETS_ASSOC_ID_CLOTH = "id_cloth";
	private static final String CLOTHES_SETS_ASSOC_ID_SET = "id_set";

	// ----------------------------------------------------

	public MobilizatorDb(Context ctx) {
		if (dh == null)
			dh = new MyDatabaseHelper(ctx);
		context = ctx;
	}

	// -------- INNER CLASS FOR HANDLING DB -------------
	class MyDatabaseHelper extends SQLiteOpenHelper {
		MyDatabaseHelper(Context context) {
			super(context, "mobilizator.db", null, 2);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL("CREATE TABLE " + TBL_CLOTHING_ATTRIBS
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "name TEXT NOT NULL, type INTEGER NOT NULL" + ");");

			db.execSQL("CREATE TABLE " + TBL_CLOTHES
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "name TEXT NOT NULL, description TEXT);");

			db.execSQL("CREATE TABLE "
					+ TBL_CATEGORIES
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "name TEXT NOT NULL, description TEXT, parent INTEGER NOT NULL);");

			db.execSQL("CREATE TABLE "
					+ TBL_CLOTHES_ATRIBS_ASSOC
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "id_cloth INTEGER NOT NULL, "
					+ "id_attrib INTEGER NOT NULL, value INTEGER NOT NULL, FOREIGN KEY(id_cloth) REFERENCES "
					+ TBL_CLOTHES + "(_id), FOREIGN KEY(id_attrib) REFERENCES "
					+ TBL_CLOTHING_ATTRIBS + "(_id)" + ");");

			db.execSQL("CREATE TABLE "
					+ TBL_CLOTHES_CATS_ASSOC
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "id_cloth INTEGER NOT NULL, "
					+ "id_cat INTEGER NOT NULL, FOREIGN KEY(id_cloth) REFERENCES "
					+ TBL_CLOTHES + "(_id), FOREIGN KEY(id_cat) REFERENCES "
					+ TBL_CATEGORIES + "(_id)" + ");");

			db.execSQL("CREATE TABLE " + TBL_CLOTHSETS
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "name TEXT NOT NULL, " + "description TEXT);");

			db.execSQL("CREATE TABLE "
					+ TBL_CLOTHES_SETS_ASSOC
					+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ "id_cloth INTEGER NOT NULL, "
					+ "id_set INTEGER NOT NULL, FOREIGN KEY(id_cloth) REFERENCES "
					+ TBL_CLOTHES + "(_id), FOREIGN KEY(id_set) REFERENCES "
					+ TBL_CLOTHSETS + "(_id)" + ");");

			// INSERTS:
			db.execSQL("INSERT INTO " + TBL_CATEGORIES
					+ "(name, description, parent) values ('"
					+ context.getString(R.string.head)
					+ "', 'Kategoria g��wna', -1);");
			db.execSQL("INSERT INTO " + TBL_CATEGORIES
					+ "(name, description, parent) values ('"
					+ context.getString(R.string.torso)
					+ "', 'Kategoria g��wna', -1);");
			db.execSQL("INSERT INTO " + TBL_CATEGORIES
					+ "(name, description, parent) values ('"
					+ context.getString(R.string.legs)
					+ "', 'Kategoria g��wna', -1);");
			db.execSQL("INSERT INTO " + TBL_CATEGORIES
					+ "(name, description, parent) values ('"
					+ context.getString(R.string.feet)
					+ "', 'Kategoria g��wna', -1);");
			db.execSQL("INSERT INTO " + TBL_CATEGORIES
					+ "(name, description, parent) values ('"
					+ context.getString(R.string.additions)
					+ "', 'Kategoria g��wna', -1);");
			db.execSQL("INSERT INTO " + TBL_CATEGORIES
					+ "(name, description, parent) values ('"
					+ context.getString(R.string.external)
					+ "', 'Kategoria g��wna', -1);");

			db.execSQL("INSERT INTO " + TBL_CLOTHING_ATTRIBS
					+ "(name, type) values ('Gdzie', "
					+ ClothingAttribute.ATTRIB_TYPE_STRING + ");");
			db.execSQL("INSERT INTO " + TBL_CLOTHING_ATTRIBS
					+ "(name, type) values ('Deszcz', "
					+ ClothingAttribute.ATTRIB_TYPE_BOOLEAN + ");");
			db.execSQL("INSERT INTO " + TBL_CLOTHING_ATTRIBS
					+ "(name, type) values ('Temperatura', "
					+ ClothingAttribute.ATTRIB_TYPE_LONG + ");");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + TBL_CLOTHING_ATTRIBS);
			db.execSQL("DROP TABLE IF EXISTS " + TBL_CLOTHES);
			db.execSQL("DROP TABLE IF EXISTS " + TBL_CATEGORIES);
			db.execSQL("DROP TABLE IF EXISTS " + TBL_CLOTHES_ATRIBS_ASSOC);
			db.execSQL("DROP TABLE IF EXISTS " + TBL_CLOTHES_CATS_ASSOC);
			db.execSQL("DROP TABLE IF EXISTS " + TBL_CLOTHSETS);

			onCreate(db);
		}

	}

	public void openForever() {
		db = dh.getWritableDatabase();
	}

	public void insertClothSet(ClothSet clothSet) {
		ArrayList<Cloth> clothes = new ArrayList<Cloth>();

		String insert = "INSERT INTO " + TBL_CLOTHSETS + "(" + CLOTHSETS_NAME
				+ ", " + CLOTHSETS_DESCRIPTION + ") values (?, ?, ?)";

		SQLiteStatement insertSt = db.compileStatement(insert);
		insertSt.bindString(1, clothSet.getName());
		insertSt.bindString(2, clothSet.getDescription());

		insertSt.execute();

		long id = getLastInsertId(TBL_CLOTHSETS);
		clothSet.setId(id);

		String insertCats = "INSERT INTO " + TBL_CLOTHES_SETS_ASSOC + "("
				+ CLOTHES_SETS_ASSOC_ID_CLOTH + ", "
				+ CLOTHES_SETS_ASSOC_ID_SET + ") values (?, ?)";

		SQLiteStatement insertCatsSt = db.compileStatement(insertCats);

		for (int i = 0; i < clothes.size(); i++) {
			Cloth cloth = clothes.get(i);

			insertCatsSt.bindLong(1, cloth.getId());
			insertCatsSt.bindLong(2, clothSet.getId());

			insertCatsSt.execute();
		}
	}

	public Cloth getCloth(long clothId) {
		Cursor c = db.query(TBL_CLOTHES, new String[] { CLOTHES_ID,
				CLOTHES_NAME, CLOTHES_DESCRIPTION }, CLOTHES_ID + " = "
				+ clothId, null, null, null, null);
		int idIndex = c.getColumnIndex(CLOTHES_ID);
		int nameIndex = c.getColumnIndex(CLOTHES_NAME);
		int descIndex = c.getColumnIndex(CLOTHES_DESCRIPTION);

		c.moveToFirst();

		long id = c.getLong(idIndex);
		String name = c.getString(nameIndex);
		String desc = c.getString(descIndex);

		Cloth cloth = new Cloth(name);
		cloth.setId(id);
		cloth.setDescription(desc);

		return cloth;
	}

	public ArrayList<ClothSet> getClothSets() {
		ArrayList<ClothSet> clothSets = new ArrayList<ClothSet>();

		Cursor c = db.query(TBL_CLOTHSETS, new String[] { CLOTHSETS_ID,
				CLOTHSETS_NAME, CLOTHSETS_DESCRIPTION }, null, null, null,
				null, null);

		c.moveToPosition(-1);

		int idIndex = c.getColumnIndex(CLOTHSETS_ID);
		int nameIndex = c.getColumnIndex(CLOTHSETS_NAME);
		int descIndex = c.getColumnIndex(CLOTHSETS_DESCRIPTION);

		while (c.moveToNext()) {
			long id = c.getLong(idIndex);
			String name = c.getString(nameIndex);
			String desc = c.getString(descIndex);

			ClothSet cs = new ClothSet(name);
			cs.setId(id);
			cs.setDescription(desc);

			Cursor curSets = db.query(TBL_CLOTHES_SETS_ASSOC, new String[] {
					CLOTHES_SETS_ASSOC_ID_CLOTH, CLOTHES_SETS_ASSOC_ID_SET },
					CLOTHES_SETS_ASSOC_ID_SET + " = " + cs.getId(), null, null,
					null, null);

			int clothIdIndex = curSets
					.getColumnIndex(CLOTHES_SETS_ASSOC_ID_CLOTH);

			curSets.moveToPosition(-1);

			ArrayList<Cloth> clothes = new ArrayList<Cloth>();
			while (curSets.moveToNext()) {
				long clothId = curSets.getLong(clothIdIndex);
				Cloth cloth = getCloth(clothId);
				clothes.add(cloth);
			}
			cs.setClothes(clothes);

		}

		return clothSets;
	}

	/**
	 * PAMI�TAJ DO category WPISA� ID OJCA!!!!!
	 * 
	 * @param category
	 */
	public void insertCategory(Category category) {
		String insert = "INSERT INTO " + TBL_CATEGORIES + "(" + CATEGORIES_NAME
				+ ", " + CATEGORIES_DESCRIPTION + ", " + CATEGORIES_PARENT
				+ ") values (?, ?, ?)";

		SQLiteStatement insertSt = db.compileStatement(insert);
		insertSt.bindString(1, category.getName());
		insertSt.bindString(2, category.getDescription());
		insertSt.bindLong(3, category.getIdParent());

		insertSt.execute();
	}

	public ArrayList<Category> getUserCategories() {
		ArrayList<Category> userCategories = new ArrayList<Category>();
		Cursor c = db.query(TBL_CATEGORIES, new String[] { CATEGORIES_ID,
				CATEGORIES_NAME, CATEGORIES_DESCRIPTION, CATEGORIES_PARENT },
				CATEGORIES_PARENT + " != -1", null, null, null, null);
		c.moveToPosition(-1);

		int idIndex = c.getColumnIndex(CATEGORIES_ID);
		int nameIndex = c.getColumnIndex(CATEGORIES_NAME);
		int descIndex = c.getColumnIndex(CATEGORIES_DESCRIPTION);
		int parentIndex = c.getColumnIndex(CATEGORIES_PARENT);

		while (c.moveToNext()) {
			long id = c.getLong(idIndex);
			String name = c.getString(nameIndex);
			String desc = c.getString(descIndex);
			long parentId = c.getLong(parentIndex);
			Category cat = new Category(name);

			cat.setDescription(desc);
			cat.setId(id);
			cat.setIdParent(parentId);

			userCategories.add(cat);
		}

		return userCategories;
	}

	public ArrayList<ClothingAttribute> getClothingAttributes() {
		Cursor c = db.query(TBL_CLOTHING_ATTRIBS, new String[] {
				CLOTHING_ATTRIBS_ID, CLOTHING_ATTRIBS_NAME,
				CLOTHING_ATTRIBS_TYPE }, null, null, null, null,
				CLOTHING_ATTRIBS_ID + "ASC");

		c.moveToPosition(-1);

		ArrayList<ClothingAttribute> attribs = new ArrayList<ClothingAttribute>();

		int idIndex = c.getColumnIndex(CLOTHING_ATTRIBS_ID);
		int nameIndex = c.getColumnIndex(CLOTHING_ATTRIBS_NAME);
		int typeIndex = c.getColumnIndex(CLOTHING_ATTRIBS_TYPE);

		while (c.moveToNext()) {
			long id = c.getLong(idIndex);
			String name = c.getString(nameIndex);
			int type = c.getInt(typeIndex);

			ClothingAttribute ca = new ClothingAttribute(name);

			ca.setId(id);
			ca.setType(type);
			attribs.add(ca);
		}

		c.close();

		return attribs;
	}

	public void insertCloth(Cloth cloth) {

		Log.d("ins_cloth", "wsadzamy ciucha");
		
		ArrayList<ClothingAttribute> attribs = cloth.getAttribs();
		ArrayList<Category> cats = cloth.getCategories();

		String insert = "INSERT INTO " + TBL_CLOTHES + "(" + CLOTHES_NAME
				+ ", " + CLOTHES_DESCRIPTION + ") values (?, ?)";

		SQLiteStatement insertSt = db.compileStatement(insert);
		insertSt.bindString(1, cloth.getName());
		insertSt.bindString(2, cloth.getDescription());

		insertSt.execute();

		long id = getLastInsertId(TBL_CLOTHES);
		cloth.setId(id);

		// add categories
		String insertCats = "INSERT INTO " + TBL_CLOTHES_CATS_ASSOC + "("
				+ CLOTHES_CATS_ASSOC_ID_CLOTH + ", "
				+ CLOTHES_CATS_ASSOC_ID_CATEGORY + ") values (?, ?)";

		SQLiteStatement insertCatsSt = db.compileStatement(insertCats);

		for (int i = 0; i < cats.size(); i++) {
			Category cat = cats.get(i);

			insertCatsSt.bindLong(1, cloth.getId());
			insertCatsSt.bindLong(2, cat.getId());

			insertCatsSt.execute();
		}

		// add attributes
		String insertAttribs = "INSERT INTO " + TBL_CLOTHES_ATRIBS_ASSOC + "("
				+ CLOTHES_ATRIBS_ASSOC_ID_CLOTH + ", "
				+ CLOTHES_ATRIBS_ASSOC_ID_ATTRIB + ", "
				+ CLOTHES_ATRIBS_ASSOC_VALUE + ") values (?, ?, ?)";

		SQLiteStatement insertAttribsSt = db.compileStatement(insertAttribs);

		for (int i = 0; i < attribs.size(); i++) {
			ClothingAttribute attrib = attribs.get(i);

			insertCatsSt.bindLong(1, cloth.getId());
			insertCatsSt.bindLong(2, attrib.getId());
			Object value = attrib.getValue();
			if (value instanceof Boolean)
				insertCatsSt.bindString(3, ((Boolean) value).toString());
			if (value instanceof String)
				insertCatsSt.bindString(3, (String) value);

			insertCatsSt.execute();
		}
	}

	public ArrayList<Category> getMainCategories() {
		ArrayList<Category> mainCats = new ArrayList<Category>();
		Cursor c = db.query(TBL_CATEGORIES, new String[] { CATEGORIES_ID,
				CATEGORIES_NAME, CATEGORIES_DESCRIPTION }, CATEGORIES_PARENT
				+ " = -1", null, null, null, null);

		c.moveToPosition(-1);

		int idIndex = c.getColumnIndex(CATEGORIES_ID);
		int nameIndex = c.getColumnIndex(CATEGORIES_NAME);
		int descIndex = c.getColumnIndex(CATEGORIES_DESCRIPTION);

		while (c.moveToNext()) {
			long id = c.getLong(idIndex);
			String name = c.getString(nameIndex);
			String desc = c.getString(descIndex);

			Category cat = new Category(name);
			cat.setId(id);
			cat.setDescription(desc);
			cat.setIdParent(-1);

			mainCats.add(cat);
		}

		c.close();

		return mainCats;
	}

	public ArrayList<Cloth> getClothesForCategory(long catId) {
		ArrayList<Long> clothesIds = getClothIdsFromCat(catId);
		ArrayList<Cloth> clothes = new ArrayList<Cloth>();
		for (int i = 0; i < clothes.size(); i++) {
			long currentClothId = clothesIds.get(i);
			Cursor c = db.query(TBL_CLOTHES, new String[] { CLOTHES_ID,
					CLOTHES_NAME, CLOTHES_DESCRIPTION }, CLOTHES_ID + " = "
					+ currentClothId, null, null, null, null);
			c.moveToFirst();
			long clothid = c.getLong(c.getColumnIndex(CLOTHES_ID));
			String name = c.getString(c.getColumnIndex(CLOTHES_NAME));
			String description = c.getString(c
					.getColumnIndex(CLOTHES_DESCRIPTION));
			Cloth cloth = new Cloth(name);
			cloth.setId(clothid);
			cloth.setDescription(description);

			clothes.add(cloth);
		}
		return clothes;
	}

	private ArrayList<Long> getClothIdsFromCat(long catId) {
		ArrayList<Long> clothIds = new ArrayList<Long>();

		Cursor c = db.query(TBL_CLOTHES_CATS_ASSOC,
				new String[] { CLOTHES_CATS_ASSOC_ID_CLOTH },
				CLOTHES_CATS_ASSOC_ID_CATEGORY + " = " + catId, null, null,
				null, null);

		c.moveToPosition(-1);

		int clothIdIndex = c.getColumnIndex(CLOTHES_CATS_ASSOC_ID_CLOTH);

		while (c.moveToNext()) {
			long clothId = c.getLong(clothIdIndex);
			clothIds.add(clothId);
		}

		return clothIds;
	}

	private int getLastInsertId(String tableName) {
		int index = 0;
		Cursor cursor = db.query("sqlite_sequence", new String[] { "seq" },
				"name = ?", new String[] { tableName }, null, null, null, null);
		if (cursor.moveToFirst()) {
			index = cursor.getInt(cursor.getColumnIndex("seq"));
		}
		cursor.close();
		return index;
	}
}
